## 白名单处理数据库
import mysql.connector  # 或者 import PyMySQL  
from mysql.connector import Error  
from sshtunnel import SSHTunnelForwarder
import pandas as pd
import pymysql
import logging  
  
# 创建一个logger
logging.basicConfig(level=logging.INFO, format='%(asctime)s - %(levelname)s - %(message)s')
logger = logging.getLogger(__name__)
logger.setLevel(logging.DEBUG)  #



# 设置SSH服务器的参数
ssh_host = '110.188.24.107'
ssh_port = 10022
ssh_user = 'root'
ssh_password = 'HYzh20221015'
 
# 设置数据库服务器的参数（假设数据库运行在远程服务器上）
db_host = '110.188.24.186'
db_port = 13306
db_user = 'root'
db_password = 'HYzh248713@.'
db_name = 'hyzh-server'
 

# 如果中文乱码，尝试使用以下方法
def read_excel_with_encoding(file_path, sheet_name, encoding='utf-8'):
    # 使用 openpyxl 引擎读取 Excel 文件
    excel_file = pd.ExcelFile(file_path, engine='openpyxl')
    
    # 读取特定的工作表
    df = excel_file.parse(sheet_name)
    
    # 将 DataFrame 中的字符串列转换为 Unicode 编码
    for col in df.columns:
        if df[col].dtype == 'object':
            df[col] = df[col].astype(str).apply(lambda x: x.encode(encoding).decode(encoding))
    
    return df
    
# 读取 Excel 文件
df = read_excel_with_encoding('眉山高新白名单综合.xlsx', sheet_name='Sheet1')
  
# 连接到MySQL数据库  
try:  
    # 创建SSH隧道
    with SSHTunnelForwarder(
            (ssh_host, ssh_port),
            ssh_username=ssh_user,
            ssh_password=ssh_password,
            remote_bind_address=(db_host, db_port)) as server:
    
        # 连接到数据库
        conn = pymysql.connect(
            # host='127.0.0.1',  # 本地局域网地址
            # port=server.local_bind_port,
            host=db_host,
            user=db_user,
            password=db_password,
            database=db_name,
            charset='utf8mb4'
        )

        # 创建游标对象
        with conn.cursor() as cursor:

            # 将 DataFrame 写入 MySQL 数据库
            for row in df.itertuples(index=False):

                if row.carnumb == '':
                    continue

                # 执行 SQL 查询
                sql_query = """  
                    SELECT id,car_numb 
                        from t_gate_reservation_white 
                        where 
                        deleted = 0
                        and park_id = '387c0863-cfcf-4865-84df-886d9f3584de'
                        and car_numb=%s
                        limit 1;
                        """
                cursor.execute(sql_query,(row.carnumb,))
                # 获取所有查询结果
                results = cursor.fetchone()
                if results:
                    print(results)

                sql_co_info = """
                        SELECT id, company_name
                        FROM co_info
                        WHERE deleted = 0
                        AND park_id = '387c0863-cfcf-4865-84df-886d9f3584de'
                        AND company_name LIKE CONCAT('%', %s, '%')
                        LIMIT 1
                    """
                # 执行 SQL 查询
                cursor.execute(sql_co_info, (row.company,))  # 注意这里的参数传递方式
                co_results = cursor.fetchone()
                if co_results:
                    print(co_results)



    # gate_devices = execute_query(query, cursor)  
    # if gate_devices:  
    #     for device in gate_devices:  
    #         id, device_code, device_ip, device_name = device  
    #         # 假设您知道要更新的字段和值，这里只是一个示例  
    #         # 使用 UPDATE 语句批量更新 risk_hidden_danger_warning 表  
    #         # 这里的 `your_column_to_update` 是您想要更新的列，而 `'new_value'` 是新值  
    #         if  device_ip != '' and device_ip is not None:
    #             update_query = f"""  
    #             UPDATE risk_hidden_danger_warning SET relation_id ='{id}', device_code = '{device_code}' WHERE ip = '{device_ip}'  
    #             """
    #             logger.info(update_query)
    #             execute_query(update_query, cursor)
    #             connection.commit()  # 提交事务

    # else:  
    #     print('No devices found in t_gate_device')
 
except Error as e:  
    print(f"Error connecting to MySQL222: {e}")  
finally:  
    # 关闭游标和连接
    if cursor:
        cursor.close()
    if conn:
        conn.close()
    print('MySQL connection is closed1111')

def execute_query(query, cursor):  
    try:  
        cursor.execute(query)  
        return cursor.fetchall()  
    except Error as e:  
        print(f"Error executing query: {e}")  
        return None  

